/**
 * Created by shenhai on 15/10/16.
 */

var async = require('async');
var Community = mongoose.model('community');
var User = mongoose.model('user');
var toolUtil = require('../../../common/utils/toolUtil');
var moment = require('moment');
var mysqlUtil = require('../../../common/utils/mysqlUtil');



exports.roleExamineListService = function(req ,callback) {

    var body = req.body;
    var page = req.params.page;
    var size = Number(req.params.size);
    var index = (Number(req.params.page) - 1) * size;
    var totalPage = 0;

    var start = "";
    var end = ";"
    if (body.cycle) {
        start = body.cycle + "-01";
        end = moment(body.cycle + "-01").add(1, 'month').format('YYYY-MM-DD');
    } else {
        body.cycle = moment().format('YYYY-MM');
        start = body.cycle + "-01";
        end = moment(body.cycle + "-01").add(1, 'month').format('YYYY-MM-DD');
    };


    var sql = "";
    var sqlCount = "";

    sqlCount += "    select count(A._id) count from  66xiaoqu_rec.user A \n";
    sqlCount += "    inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 1 \n";

    var userSql = "";

    if (body.community) {
        userSql += " and A.community ='" + body.community + "'";
        sqlCount += " and A.community ='" + body.community + "'";
    };

    if (body.nickName) {
        userSql += " and A.nickName like'%" + body.nickName + "%'";
        sqlCount += " and A.nickName like'%" + body.nickName + "%'";
    };

    if (body.mobilePhone) {
        userSql += " and A.mobilePhone like'%" + body.mobilePhone + "%'";
        sqlCount += " and A.mobilePhone like'%" + body.mobilePhone + "%'";
    };

    sql += "  select P._id,D.community,CO.communityName,D.nickName,D.roomUnit,D.mobilePhone,postCount,replyCount,pushCount,topCount,levelCount,aduCount from \n";
    sql += "  ( ";
    sql += "      -- 发帖数 \n";
    sql += "    select A._id , count(C.createdBy) postCount from  66xiaoqu_rec.user A \n";
    sql += "    inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 1 \n";
    sql += userSql;
    sql += "    left join 66xiaoqu_rec.question C on  C.createdBy = A._id  and  (C.dateCreated>='" + start + "' and C.dateCreated <'" + end + "') \n";
    sql += "    group by A._id \n";
    sql += "  ) P \n";

    sql += "  inner join \n";
    sql += "  ( \n";
    sql += "      -- 回复数 \n";
    sql += "    select A._id , count(C.repliedBy) replyCount from  66xiaoqu_rec.user A \n";
    sql += "    inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 1 \n";
    sql += userSql;
    sql += "    left join 66xiaoqu_rec.question_replies C on  C.repliedBy = A._id  and  (C.dateReplied>='" + start + "' and C.dateReplied <'" + end + "') \n";
    sql += "    group by A._id \n";
    sql += "  )R on P._id = R._id \n";

    sql += "  inner join \n";
    sql += "  ( \n";
    sql += "      -- 推送数 \n";
    sql += "    select A._id , count(C.action) pushCount from  66xiaoqu_rec.user A \n";
    sql += "    inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 1 \n";
    sql += userSql;
    sql += "    left join 66xiaoqu_log.user_action_log C on  C.user_id = A._id and C.action ='push' and  (C.dateCreat>='" + start + "' and C.dateCreat <'" + end + "') \n";
    sql += "    group by A._id \n";
    sql += "  )PS on PS._id = R._id \n";

    sql += "  inner join \n";
    sql += "  ( \n";
    sql += "      -- 置顶数 \n";
    sql += "    select A._id , count(C.action) topCount from  66xiaoqu_rec.user A \n";
    sql += "    inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 1 \n";
    sql += userSql;
    sql += "    left join 66xiaoqu_log.user_action_log C on  C.user_id = A._id and C.action ='top' and  (C.dateCreat>='" + start + "' and C.dateCreat <'" + end + "') \n";
    sql += "    group by A._id \n";
    sql += "  ) T on T._id = R._id \n";

    sql += "  inner join \n";
    sql += "  ( \n";
    sql += "      -- 置精华数 \n";
    sql += "    select A._id , count(C.action) levelCount from  66xiaoqu_rec.user A \n";
    sql += "    inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 1 \n";
    sql += userSql;
    sql += "    left join 66xiaoqu_log.user_action_log C on  C.user_id = A._id and C.action ='level' and  (C.dateCreat>='" + start + "' and C.dateCreat <'" + end + "') \n";
    sql += "    group by A._id \n";
    sql += "  ) L on L._id = R._id \n";
    sql += "  inner join \n";
    sql += "  ( \n";
    sql += "      -- 活跃度 \n";
    sql += "    select A._id, aduCount from  66xiaoqu_rec.user A ";
    sql += "    inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 1 \n";
    sql += userSql;
    sql += "    left join \n";
    sql += "    ( \n";
    sql += "      select uid,count(actionDate)aduCount from \n";
    sql += "      ( \n";
    sql += "        select distinct uid,actionDate from 66xiaoqu_rec.dau C \n";
    sql += "         where  (C.actionDate>='" + start + "' and C.actionDate <'" + end + "') \n";
    sql += "      )D group by uid \n";
    sql += "    )E on E.uid = A._id \n";
    sql += "  )DA on DA._id = R._id \n";

    sql += "  inner join 66xiaoqu_rec.user D on P._id = D._id \n";
    sql += "  inner join community CO on CO._id = D.community \n";
    sql += "  order by  communityName  \n";
    sql += "  limit " + index + "," + size;

    logger.debug(sql);

    async.series([
        function (callbackInner) {

            mysqlUtil.queryBI(sqlCount, function (err, result) {

                var count = result[0].count;
                totalPage = Math.ceil(count / size);
                var temp = {count: count, totalPage: totalPage};
                callbackInner(err, temp);
            });
        },

        function (callbackInner) {
            mysqlUtil.queryBI(sql, function (err, result) {
                callbackInner(err, result);
            });
        },


    ], callback);

};


exports.roleLeifengListService = function(req ,callback) {

    var body = req.body;
    var page = req.params.page;
    var size = Number(req.params.size);
    var index = (Number(req.params.page) - 1) * size;
    var totalPage = 0;

    //var option = {userRole:body.userRole};
    //var sortType = {dateCreated:-1};
    //
    //if (body.community) option.community = body.community;
    //if (body.nickName) option.nickName = new RegExp(body.nickName);
    //if (body.mobilePhone) option.mobilePhone = new RegExp(body.mobilePhone);
    //
    //if (body.startDate || body.endDate) {
    //    if (body.startDate && body.endDate) {
    //        if (body.startDate < body.endDate) {
    //            option.dateCreated = {$gte: body.startDate, $lte: body.endDate};
    //        }
    //    } else if (body.startDate) {
    //        option.dateCreated = {$gte: body.startDate};
    //    } else if (body.endDate) {
    //        option.dateCreated = {$lte: body.endDate};
    //    }
    //}

    var start = "";
    var end = "";
    if (body.cycle) {
        start = body.cycle + "-15";
        end = moment(body.cycle + "-15").add(1, 'month').format('YYYY-MM-DD');
    } else {
        body.cycle = moment().format('YYYY-MM');
        start = body.cycle + "-15";
        end = moment(body.cycle + "-15").add(1, 'month').format('YYYY-MM-DD');
    };


    var sqlCount = "";
    sqlCount += "    select count(A._id) count from  66xiaoqu_rec.user A \n";
    sqlCount += "    inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 3 \n";

    var userSql = "";

    if (body.community) {
        userSql += " and A.community ='" + body.community + "'";
        sqlCount += " and A.community ='" + body.community + "'";
    };

    if (body.nickName) {
        userSql += " and A.nickName like'%" + body.nickName + "%'";
        sqlCount += " and A.nickName like'%" + body.nickName + "%'";
    };

    if (body.mobilePhone) {
        userSql += " and A.mobilePhone like'%" + body.mobilePhone + "%'";
        sqlCount += " and A.mobilePhone like'%" + body.mobilePhone + "%'";
    };

    var sql = "";

    sql += "select U._id,U.community,CO.communityName,U.nickName,U.roomUnit ,URA.address,mobilePhone,ifnull(postCount,0)postCount,ifnull(thankCount,0)thankCount \n";
    sql += "    ,ifnull(adoptCount,0)adoptCount,ifnull(inviteCount,0)	inviteCount,ifnull(smartPushCount,0)smartPushCount,ifnull(pushReplyCount,0)pushReplyCount\n";
    sql += "    ,concat(ifnull(round (ifnull(pushReplyCount,0)/ifnull(smartPushCount,0) *100,0),0),'%')pushReplyRatio ,ifnull(replayCount,0)replayCount,ifnull(levelCount,0)levelCount \n";
    sql += "    ,ifnull(postCount,0)*2  + ifnull(levelCount,0)*5 + (ifnull(replayCount,0)*1 + ifnull(thankCount,0)*1 + ifnull(adoptCount,0)*5 + ifnull(inviteCount,0)*1)score \n";
    sql += "from \n";
    sql += "( \n";
    sql += "    select A._id , nickName,roomUnit,mobilePhone,community from  66xiaoqu_rec.user A \n";
    sql += "    inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 3 \n";
    sql += userSql;
    sql += ")U \n";

    sql += "left join \n";
    sql += "( \n";
    sql += "    -- 发帖数 \n";
    sql += "select A._id , count(C.createdBy) postCount from  66xiaoqu_rec.user A \n";
    sql += "inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 3 \n";
    sql += userSql + '\n';
    sql += "inner join 66xiaoqu_rec.question C on  C.createdBy = A._id  and  (C.dateCreated>='" + start + "' and C.dateCreated <'" + end + "') \n";
    sql += "group by A._id \n";
    sql += ") P on U._id = P._id \n";

    sql += "left join\n";
    sql += "(\n";
    sql += "    -- 发帖的精华贴数\n";
    sql += "select A._id , count(C.createdBy ) levelCount from  66xiaoqu_rec.user A \n";
    sql += "inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 3 \n";
    sql += "inner join 66xiaoqu_rec.question C on C.createdBy  = A._id and C.level = 1 and C.dateCreated>='" + start + "' and  C.dateCreated <'" + end + "' \n";
    sql += "group by A._id \n";
    sql += ")L on  U._id = L._id \n";

    sql += "left join \n";
    sql += "( \n";
    sql += "    -- 回复数 \n";
    sql += "select A._id , count(C.repliedBy) replayCount from  66xiaoqu_rec.user A \n";
    sql += "inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 3 \n";
    sql += userSql + '\n';
    sql += "inner join 66xiaoqu_rec.question_replies C on  C.repliedBy = A._id  and  (C.dateReplied>='" + start + "' and C.dateReplied <'" + end + "') \n";
    sql += "group by A._id \n";

    sql += ")R on U._id = R._id \n";

    sql += "left join \n";
    sql += "( \n";
    sql += "    -- 回复被感谢 \n";
    sql += "select A._id , count(action) thankCount from  66xiaoqu_rec.user A \n";
    sql += "inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 3 \n";
    sql += userSql + '\n';
    sql += "inner join 66xiaoqu_rec.questions2 C on C.__replyBy = A._id and action ='thankReply' and  ( C.Datetime>='" + start + "' and  C.Datetime <'" + end + "') \n";
    sql += "group by A._id \n";
    sql += ")THA on  U._id = THA._id \n";

    sql += "left join \n";
    sql += "( \n";
    sql += "   -- 回复被采纳 \n";
    sql += "select A._id , count(action) adoptCount from  66xiaoqu_rec.user A \n";
    sql += "inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 3 \n";
    sql += userSql + '\n';
    sql += "inner join 66xiaoqu_rec.question_replies D on D.repliedBy = A._id \n";
    sql += "inner join 66xiaoqu_rec.questions2 C on  C.__replyId = D._id and action ='adoptReply' and  ( C.Datetime>='" + start + "' and  C.Datetime <'" + end + "') \n";
    sql += "group by A._id \n";

    sql += ")ADO on  U._id = ADO._id \n";

    sql += "left join \n";
    sql += "( \n";
    sql += "    -- 邀请人数 \n";
    sql += "select A._id , count(C._id ) inviteCount from  66xiaoqu_rec.user A \n";
    sql += "inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 3 \n";
    sql += userSql + '\n';
    sql += "inner join 66xiaoqu_rec.user C on C.invitedBy  = A._id and C.dateCreated>='" + start + "' and  C.dateCreated <'" + end + "' \n";
    sql += "group by A._id \n";

    sql += ")INV on  U._id = INV._id \n";
    sql += "-- \n";

    sql += "left join \n";
    sql += "( \n";
    sql += "    -- 收到推送问题的次数 \n";
    sql += "select A._id , count(C._id ) smartPushCount from  66xiaoqu_rec.user A \n";
    sql += "inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 3 \n";
    sql += userSql + '\n';
    sql += "inner join 66xiaoqu_rec.smartpush_sentusers C on C.sentUser  = A._id and C.pushTime>='" + start + "' and  C.pushTime <'" + end + "' \n";
    sql += "group by A._id \n";

    sql += ")SMP on  U._id = SMP._id \n";

    sql += "left join \n";
    sql += "( \n";
    sql += "    -- 收到推送问题的h回复次数 \n";
    sql += "select A._id , count(C.sentUser) pushReplyCount from  66xiaoqu_rec.user A \n";
    sql += "inner join 66xiaoqu_rec.user_userRole B on A._id = B.user_id and B.userRole = 3 \n";
    sql += userSql + '\n';
    sql += "inner join 66xiaoqu_rec.smartpush_sentusers C on C.sentUser  = A._id and C.pushTime>='" + start + "' and  C.pushTime <'" + end + "' \n";
    sql += "inner join 66xiaoqu_rec.question_replies D on D.question_id = C._id and D.repliedBy = A._id \n";
    sql += "group by A._id \n";
    sql += ")PR on U._id = PR._id \n";

    sql += "inner join 66xiaoqu_rec.community CO on CO._id = U.community \n";

    sql += "left join \n";
    sql += "( \n";
    sql += "   -- 雷锋申请填写的地址 \n";
    sql += "select V1.user, V1.address from 66xiaoqu_rec.user_role_apply V1 \n";
    sql += "inner join \n";
    sql += "( \n";
    sql += "    select user,max(dateCreated)dateCreated from 66xiaoqu_rec.user_role_apply \n";
    sql += "where status = 1 \n";
    sql += "group by user \n";
    sql += ") V2 on V1.user = V2.user and V1.dateCreated = V2.dateCreated and V1.status = 1 \n";

    sql += ")URA on U._id = URA.user \n";
    sql += "  order by  score desc  \n";
    sql += "  limit " + index + "," + size;

    logger.debug(sql);
    async.series([
        function (callbackInner) {
            mysqlUtil.queryBI(sqlCount, function (err, result) {

                var count = result[0].count;
                totalPage = Math.ceil(count / size);
                var temp = {count: count, totalPage: totalPage};
                callbackInner(err, temp);
            });
        },

        //function (callbackInner) {
        //    User.find(option)
        //        .populate('community', 'communityName')
        //        .sort(sortType).skip(index).limit(size).exec(function (err, result) {
        //
        //            async.eachSeries(result, function (temp, callbackInner2) {
        //
        //                var key = 'user:' + temp._id + ':app:data:statistic';
        //                var opt = [key, 'question', 'questionReply', 'praise', 'goods'];
        //                redisClient.hmget(opt, function (err, result) {
        //                    temp._doc.questionCount = result[0] ? result[0] : 0;
        //                    temp._doc.replyCount = result[1] ? result[1] : 0;
        //                    temp._doc.praiseCount = result[2] ? result[2] : 0;
        //                    temp._doc.goodsCount = result[3] ? result[3] : 0;
        //                    callbackInner2(err);
        //                });
        //            }, function (err) {
        //                callbackInner(err, result);
        //            });
        //
        //            callbackInner(err, result);
        //        });
        //},

        function (callbackInner) {
            mysqlUtil.queryBI(sql, function (err, result) {
                callbackInner(err, result);
            });
        },


    ], callback);

};